﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using fuzhu;
using System.Data.SqlClient;

namespace GLY_XieTong
{

    public class XieTong
    {


        // A,B 账连接字符串
        public string cConStrA { get; set; }
        public string cConStrB { get; set; }

        public string cZTA { get; set; }
        public string iYearA { get; set; }

        public string cZTB { get; set; }
        public string iYearB { get; set; }
        //中间库
        public string cConStr { get; set; }
        public string cZJDATA { get; set; }  //中间库名

        public string cSourceCodeColA;
        public string cSourceCodeColB;
        public string cTargetCodeColA;
        public string cTargetCodeColB;


        public XieTong(string C)
        {

            cConStr = C;
            cZJDATA = GetInterDataBase();

            string cDataA = GetDataA();
            cConStrA = cConStr.Replace(cZJDATA, cDataA);
            cZTA = cDataA.Substring(7, 3);
            iYearA = cDataA.Substring(11, 4);

            string cDataB = GetDataB();
            cConStrB = cConStr.Replace(cZJDATA, cDataB);
            cZTB = cDataB.Substring(7, 3);
            iYearB = cDataB.Substring(11, 4);

            cSourceCodeColA = GetSourceCodeColA();
            cSourceCodeColB = GetSourceCodeColB();
            cTargetCodeColA = GetTargetCodeColA();
            cTargetCodeColB = GetTargetCodeColB();
        }

        #region  基础数据获取
        /// <summary>
        /// 不用了
        /// </summary>
        /// <param name="csourceCodeColA"></param>
        /// <param name="csourceCodeColB"></param>
        /// <param name="ctargetCodeColA"></param>
        /// <param name="ctargetCodeColB"></param>
        public void setPara(string csourceCodeColA, string csourceCodeColB, string ctargetCodeColA, string ctargetCodeColB)
        {
            cSourceCodeColA = csourceCodeColA;
            cSourceCodeColB = csourceCodeColB;
            cTargetCodeColA = ctargetCodeColA;
            cTargetCodeColB = ctargetCodeColB;
        }
        /// <summary>
        /// 中间库
        /// </summary>
        /// <returns></returns>
        public string GetInterDataBase()
        {
            string sql = @"select DB_NAME()";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
        }
        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConStrA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='7'";
            string cDataA = DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));
            return cConStr.Replace(cZJDATA, cDataA);
        }
        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetDataA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='7'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConStrB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='8'";
            string cDataB = DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));
            return cConStr.Replace(cZJDATA, cDataB);

        }

        public string GetDataB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='8'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }
        /// <summary>
        /// 获取A的 来源单据 栏目名称
        /// </summary>
        /// <param name="cDateInter">中间库</param>
        /// <returns></returns>
        public string GetSourceCodeColA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='9'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取A的 去向单据 栏目名称
        /// </summary>
        /// <returns></returns>
        public string GetTargetCodeColA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='10'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取B的 来源单据 栏目名称
        /// </summary>
        /// <returns></returns>
        public string GetSourceCodeColB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='11'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取B的 去向单据 栏目名称
        /// </summary>
        /// <returns></returns>
        public string GetTargetCodeColB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='12'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取A的 表体来源单据id
        /// </summary>
        /// <returns></returns>
        public string GetSourceColsA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='13'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }


        /// <summary>
        /// 获取A的 表体来源单据id
        /// </summary>
        /// <returns></returns>
        public string GetSourceColsB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='14'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取协同的 客户信息
        /// </summary>
        /// <returns></returns>
        public string GetcCusCode()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='1'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取协同的 客户信息
        /// </summary>
        /// <returns></returns>
        public string GetcCusName()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='2'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }
        /// <summary>
        /// 获取协同的 客户信息
        /// </summary>
        /// <returns></returns>
        public string GetcCusAddress()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='3'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取协同的 供应商信息
        /// </summary>
        /// <returns></returns>
        public string GetcVenCode()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='4'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        /// <summary>
        /// 获取协同的 仓库信息
        /// </summary>
        /// <returns></returns>
        public string GetcWhcode()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='6'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }
        #endregion

        #region 基础档案插入中间表
        /// <summary>
        /// 基础档案插入中间表： 存货分类、计量单位等单表档案，用时间戳判断更新
        /// cconstr: 来源单据字符串
        /// </summary>       
        public string DangAnINSZJB(string cLx, string cSourceConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK,ctimestamp,czjbfilter FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["czjbfilter"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //构造新增等sql语句
                    //1 检查是否有新增的档案
                    sql = string.Format(@"select top 1000  {0} from {1} a where not exists(select 1 from {2} b where a.{0}=b.{0} and (b.cstatus ='0' OR b.cstatus ='1')) {3}
                ", cPK, cSourceTable, cTarTable, cFilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cSourceConstr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            string cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);
                            //2、 insert栏目，批量插入中间表，逐条更新目标库


                            //获取id
                            int iId = GetInterTableID(cIdentity);
                            sql = string.Format(" insert into {0}({1}) select {2} from {3} a where {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cInvccode);

                            SqlParameter[] param2 = new SqlParameter[]{
                new SqlParameter("@id",iId),
                 new SqlParameter("@coperation","0")
          };
                            int iRe = DbHelper2.ExecuteNonQuery(sql, param2, CommandType.Text, cSourceConstr);
                            if (iRe < 1)
                            {

                                cError += cLx + ":" + cInvccode + ":写入中间表执行不成功！" + sql + "\r\n";
                            }
                            //存货档案及客户档案处理
                            else if (cLx == "存货档案INS中间表")
                            {
                                //存货档案插入子表
                                string cRe = DangAnINSZJB_SUB("存货档案SUBINS中间表", iId, cInvccode, cSourceConstr);
                                if (cRe != "ok")
                                {
                                    cError += "存货档案SUBINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                }
                                cRe = DangAnINSZJB_SUB("存货档案BASINS中间表", iId, cInvccode, cSourceConstr);
                                if (cRe != "ok")
                                {
                                    cError += "存货档案BASINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                }
                            }
                            else if (cLx == "客户档案INS中间表")
                            {
                                //存货档案插入子表
                                string cRe = DangAnINSZJB_SUB("客户银行INS中间表", iId, cInvccode, cSourceConstr);
                                if (cRe != "ok")
                                {
                                    cError += "客户银行INS中间表:" + cInvccode + ":写入中间表执行不成功!" + cRe + "\r\n";
                                }
                                cRe = DangAnINSZJB_SUB("客户地址INS中间表", iId, cInvccode, cSourceConstr);
                                if (cRe != "ok")
                                {
                                    cError += "客户地址INS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                }
                            }
                        }

                    }
                    else
                    {
                        //2 检查是否有更改的档案，时间戳不一样
                        //先把之前执行完成的更改成2
                        sql = string.Format(@"  UPDATE  {0} SET cstatus = 2  WHERE cstatus<> '2'  and ID NOT IN 
(SELECT MAX(ID)  FROM {0} where (cstatus ='0' OR cstatus ='1')  GROUP BY {1})  ", cTarTable, cPK);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);

                        sql = string.Format("delete from {0}  where cstatus = 2 and DCREATEDATE<dateadd(month,-3,getdate())", cTarTable);
                        DbHelper2.ExecuteNonQuery(sql, cConStr);

                        sql = string.Format(@"select {0} from {1} a where exists(select 1 from {2} b,(SELECT MAX(ID) ID,{0}  FROM {2} where (cstatus ='0' OR cstatus ='1')  GROUP BY {0}) c 
where a.{0}=b.{0} and b.id=c.id and convert(varchar(50),a.{3})<>b.{3} )
                ", cPK, cSourceTable, cTarTable, ctimestamp);
                        DataTable dtUps = DbHelper2.Execute(sql, cSourceConstr).Tables[0];

                        if (dtUps.Rows.Count > 0)
                        {

                            for (int i = 0; i < dtUps.Rows.Count; i++)
                            {

                                string cInvccode = DbHelper.GetDbString(dtUps.Rows[i][0]);
                                //2、 insert栏目，批量插入中间表，逐条更新目标库


                                int iId = GetInterTableID(cIdentity);

                                sql = string.Format(" insert into {0}({1}) select {2} from {3} a where {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cInvccode);

                                SqlParameter[] param2 = new SqlParameter[]{
                new SqlParameter("@id",iId),
                 new SqlParameter("@coperation","1")
          };
                                int iRe = DbHelper2.ExecuteNonQuery(sql, param2, CommandType.Text, cSourceConstr);
                                if (iRe < 1)
                                {

                                    cError += cInvccode + ":写入中间表执行不成功！\r\n";
                                }
                                else if (cLx == "存货档案INS中间表")
                                {
                                    //存货档案插入子表
                                    string cRe = DangAnINSZJB_SUB("存货档案SUBINS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "存货档案SUBINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                    cRe = DangAnINSZJB_SUB("存货档案BASINS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "存货档案BASINS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                }
                                else if (cLx == "客户档案INS中间表")
                                {
                                    //存货档案插入子表
                                    string cRe = DangAnINSZJB_SUB("客户银行INS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "客户银行INS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                    cRe = DangAnINSZJB_SUB("客户地址INS中间表", iId, cInvccode, cSourceConstr);
                                    if (cRe != "ok")
                                    {
                                        cError += "客户地址INS中间表:" + cInvccode + ":写入中间表执行不成功！" + cRe + "\r\n";
                                    }
                                }
                            }

                        }
                    }
                    if (cError != "")
                    {
                        return "error:" + cError;
                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion
        #region 获得中间表的id
        /// <summary>
        /// 获得中间表的id
        /// </summary>
        /// <param name="cIdentity">表标识</param>
        /// <returns></returns>
        private int GetInterTableID(string cIdentity)
        {
            //获取id
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@cVouchType",cIdentity),
                 new SqlParameter("@iAmount",1),
                  new SqlParameter("@iFatherId",SqlDbType.Int),
                 new SqlParameter("@iChildId",SqlDbType.Int)
          };
            param[2].Direction = ParameterDirection.Output;
            param[3].Direction = ParameterDirection.Output;
            DbHelper2.ExecuteNonQuery("sp_Inter_GetID", param, CommandType.StoredProcedure, cConStr);
            int iId = DbHelper.GetDbInt(param[2].Value);
            return iId;
        }

        /// <summary>
        /// 获得中间表的id
        /// </summary>
        /// <param name="cIdentity">表标识</param>
        /// <returns></returns>
        private int GetInterTableID(string cIdentity, int iAmount, out int iChildid)
        {
            //获取id
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@cVouchType",cIdentity),
                 new SqlParameter("@iAmount",iAmount),
                  new SqlParameter("@iFatherId",SqlDbType.Int),
                 new SqlParameter("@iChildId",SqlDbType.Int)
          };
            param[2].Direction = ParameterDirection.Output;
            param[3].Direction = ParameterDirection.Output;
            DbHelper2.ExecuteNonQuery("sp_Inter_GetID", param, CommandType.StoredProcedure, cConStr);
            int iId = DbHelper.GetDbInt(param[2].Value);
            iChildid = DbHelper.GetDbInt(param[3].Value);
            return iId;
        }
        #endregion

        #region 中间表插入基础档案
        /// <summary>
        /// 基础档案插入中间表： 存货分类、计量单位等单表档案，用时间戳判断更新
        /// </summary>
        public string ZJBINSDangAn(string cLx, string cTarConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //1 检查未更新的中间表档案，根据编码判断是否新增，不用operation
                    sql = string.Format(@"select {0},id from {1} a where  cstatus ='0' ", cPK, cSourceTable);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        string cInvccode = "";
                        int iId = 0;
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            try
                            {
                                cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);  //档案编码
                                iId = DbHelper.GetDbInt(dtIns.Rows[i][1]);   //中间表主键
                                //2、 查询目标库是否有, 没有写事务
                                sql = string.Format("select count(1) as cnt from {0} where {1}='{2}' ", cTarTable, cPK, cInvccode);
                                int iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cTarConstr));
                                if (iCnt == 0)
                                {
                                    //新增
                                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where id='{4}'", cTarTable, cColumn, cValue, cSourceTable, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe == 1)
                                    {
                                        //存货档案，执行子表的更新
                                        if (cLx == "中间表TO存货档案")
                                        {
                                            string cRe = ZJBINSDangAnSub("中间表TO存货档案SUB", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案SUB:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAnSub("中间表TO存货档案BAS", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案BAS:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                        }
                                        else if (cLx == "中间表TO客户档案")  // 客户档案                                     
                                        {
                                            string cRe = ZJBINSDangAn2("中间表TO客户银行", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户银行" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAn2("中间表TO客户地址", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户地址:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }

                                        }
                                        if (cError != "")
                                        {
                                            sql = string.Format("update {0} set cstatus = 99,cremark = '{2}' where id = '{1}'", cSourceTable, iId, cError);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                        else
                                        {
                                            sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}'", cSourceTable, iId);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                    }
                                    else
                                    {
                                        cError += cLx + ":" + cInvccode + ":写入目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }
                                else
                                {
                                    //修改, a,b 不要搞错。 原表设置为a
                                    sql = string.Format("update {0} set {1} from {0} b,{4} a   where a.{2}=b.{2} and  a.{2}='{3}' and a.id ={5} ", cTarTable, cUpdateColunm, cPK, cInvccode, cSourceTable, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe >= 1)
                                    {
                                        //存货档案，执行子表的更新
                                        if (cLx == "中间表TO存货档案")
                                        {
                                            string cRe = ZJBINSDangAnSub("中间表TO存货档案SUB", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案SUB:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAnSub("中间表TO存货档案BAS", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO存货档案BAS:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                        }
                                        else if (cLx == "中间表TO客户档案")  // 客户档案                                     
                                        {
                                            string cRe = ZJBINSDangAn2("中间表TO客户银行", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户银行" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }
                                            cRe = ZJBINSDangAn2("中间表TO客户地址", iId, cTarConstr);
                                            if (cRe != "ok")
                                            {
                                                cError += "中间表TO客户地址:" + cInvccode + ":写入目标表执行不成功！\r\n";
                                            }

                                        }
                                        if (cError != "")
                                        {
                                            sql = string.Format("update {0} set cstatus = 99,cremark = '{2}' where id = '{1}'", cSourceTable, iId, cError);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                        else
                                        {
                                            sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}'", cSourceTable, iId);
                                            DbHelper2.ExecuteNonQuery(sql, cConStr);
                                        }
                                    }
                                    else
                                    {
                                        cError += cLx + ":" + cInvccode + ":更新目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }


                            }
                            catch (Exception ex)
                            {
                                //WriteLog wr = new WriteLog();
                                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                                sql = string.Format("update {0} set cstatus = 99,cremark='{2}' where id = '{1}'", cSourceTable, iId, ex.Message);
                                DbHelper2.ExecuteNonQuery(sql, cConStr);
                                continue;
                            }
                        }
                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {

                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion


        #region 子表插入中间表
        /// <summary>
        /// 子表查询，id使用存货档案的id，ccode代表存货编码
        /// </summary>
        /// <param name="cLx">类型</param>
        /// <param name="ID">存货档案的id</param>
        /// <param name="cCode">存货编码</param>
        /// <returns></returns>
        public string DangAnINSZJB_SUB(string cLx, int iID, string cCode, string cSourceConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK,ctimestamp FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //插入新增档案

                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cCode);

                    SqlParameter[] param2 = new SqlParameter[]{
                new SqlParameter("@id",iID),
          };
                    int iRe = DbHelper2.ExecuteNonQuery(sql, param2, CommandType.Text, cSourceConstr);
                    if (iRe < 1)
                    {

                        cError += cLx + ":" + cCode + ",写入中间表执行不成功！\r\n";
                    }


                }
                if (cError != "")
                {
                    return "error:" + cError;
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region 中间表子表插入基础档案
        /// <summary>
        /// 子表插入，根据主表的id进行插入
        /// </summary>
        public string ZJBINSDangAnSub(string cLx, int iId, string cTarConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //1 检查未更新的中间表档案，根据编码判断是否新增，不用operation
                    sql = string.Format(@"select {0},id from {1} a where  id ={2} ", cPK, cSourceTable, iId);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        string cInvccode = "";

                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            try
                            {
                                cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);  //档案编码
                                //2、 查询目标库是否有
                                sql = string.Format("select count(1) as cnt from {0} where {1}='{2}' ", cTarTable, cPK, cInvccode);
                                int iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cTarConstr));
                                if (iCnt == 0)
                                {
                                    //新增
                                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where id='{6}' and  {4}='{5}'", cTarTable, cColumn, cValue, cSourceTable, cPK, cInvccode, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe < 1)
                                    {
                                        cError += cLx + ":" + cInvccode + ":写入目标表执行不成功！\r\n";
                                    }
                                }
                                else
                                {
                                    //修改, a,b 不要搞错。 原表设置为a
                                    sql = string.Format("update {0} set {1} from {0} b,{4} a   where a.{2}=b.{2} and  a.{2}='{3}' and a.id ={5} ", cTarTable, cUpdateColunm, cPK, cInvccode, cSourceTable, iId);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe > 0)
                                    {
                                        sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                    else
                                    {
                                        cError += cInvccode + ":更新目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'", cSourceTable, iId);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }

                            }
                            catch (Exception ex)
                            {
                                //WriteLog wr = new WriteLog();
                                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                                sql = string.Format("update {0} set cstatus = 99,cremark='{2}' where id = '{1}'", cSourceTable, iId, ex.Message);
                                //DbHelper2.ExecuteNonQuery(sql, cConStr);
                                continue;
                            }

                        }

                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region 中间表子表插入基础档案，两个主键
        /// <summary>
        /// 子表插入，根据主表的id进行插入
        /// </summary>
        public string ZJBINSDangAn2(string cLx, int iId, string cTarConstr)
        {
            try
            {
                string cError = "";

                string sql = string.Format(" SELECT clx,ctable,cidentity,czjbtable,PK ,PKS FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = cZJDATA + ".." + DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''   FOR XML PATH('')),1,1,'')", cLx);
                    string cValue = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //1 检查未更新的中间表档案，根据编码判断是否新增，不用operation
                    sql = string.Format(@"select {0},{1}, id from {2} a where  id ={3} ", cPK, cPKS, cSourceTable, iId);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStr).Tables[0];

                    if (dtIns.Rows.Count > 0)
                    {
                        string cInvccode = "";
                        string cInvccode2 = "";
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            try
                            {
                                cInvccode = DbHelper.GetDbString(dtIns.Rows[i][0]);  //档案编码
                                cInvccode2 = DbHelper.GetDbString(dtIns.Rows[i][1]);  //档案编码
                                                                                      //2、 查询目标库是否有

                                sql = string.Format("select count(1) as cnt from {0} where {1}='{2}' AND {3}='{4}' ", cTarTable, cPK, cInvccode, cPKS, cInvccode2);
                                int iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cTarConstr));
                                if (iCnt == 0)
                                {
                                    //新增
                                    sql = string.Format(" insert into {0}({1}) select {2} from {3} a where id='{4}' AND {5}='{6}' AND {7}='{8}'  ", cTarTable, cColumn, cValue, cSourceTable, iId, cPK, cInvccode, cPKS, cInvccode2);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe != 1)
                                    {
                                        cError += cLx + ":" + cInvccode + ":写入目标表执行不成功！\r\n";
                                    }
                                }
                                else
                                {
                                    //修改, a,b 不要搞错。 原表设置为a
                                    sql = string.Format("update {0} set {1} from {0} b,{4} a   where a.{2}=b.{2} and a.{6}=b.{6} and  a.{2}='{3}' and a.{6}='{7}' and a.id ={5} ", cTarTable, cUpdateColunm, cPK, cInvccode, cSourceTable, iId, cPKS, cInvccode2);
                                    int iRe = DbHelper2.ExecuteNonQuery(sql, cTarConstr);
                                    if (iRe >= 1)
                                    {
                                        sql = string.Format("update {0} set cstatus = 1,dupdatetime = getdate() where id = '{1}' and {2}='{3}' AND {4}='{5}'", cSourceTable, iId, cPK, cInvccode, cPKS, cInvccode2);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                    else
                                    {
                                        cError += cInvccode + ":更新目标表执行不成功！\r\n";
                                        sql = string.Format("update {0} set cstatus = 99 where id = '{1}'  and {2}='{3}' AND {4}='{5}'", cSourceTable, iId, cPK, cInvccode, cPKS, cInvccode2);
                                        DbHelper2.ExecuteNonQuery(sql, cConStr);
                                    }
                                }

                            }
                            catch (Exception ex)
                            {
                                //WriteLog wr = new WriteLog();
                                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                                sql = string.Format("update {0} set cstatus = 99,cremark='{2}' where id = '{1}'", cSourceTable, iId, ex.Message);
                                //DbHelper2.ExecuteNonQuery(sql, cConStr);
                                continue;
                            }

                        }

                    }
                }
                else
                {
                    return "error:没有配置此档案同步";
                }
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region 单据插入中间表
        /// <summary>
        /// /// <summary>
        /// 单据插入中间表，用单据id进行插入,  用时间戳判断更新
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        ///  <param name="cconstr">来源单据的连接字符串</param>
        /// <returns></returns>

        public string VoucherINSZJB(string cLx, string cfilter, string cconstrA, out int idout, out string ccode2)
        {
            try
            {
                //1 是主表  2 是子表
                string cError = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cFullTarTable1 = cZJDATA + ".DBO." + DbHelper.GetDbString(dt1.Rows[0]["ctable"]);  //全名，加上库名
                    string cFullTarTable2 = cZJDATA + ".DBO." + DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);


                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句


                    //2、 insert栏目，批量插入中间表，逐条更新目标库
                    //获取id 


                    //循环+事务.  要插入的表格，依次插入到中间表   子表autoid循环
                    int iAutoid = 0;
                    sql = string.Format("select b.{0} {1} {2}", cPKS, cFrom, cfilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cconstrA).Tables[0];
                    iId = GetInterTableID(cIdentity, dtIns.Rows.Count, out iChild);
                    iChild = iChild + 1 - dtIns.Rows.Count;  //子表id要重新计算初始值，返回的是最大值
                    //源表的连接事务
                    SqlTransaction tran = DbHelper2.BeginTrans(cconstrA);
                    try
                    {

                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            iAutoid = DbHelper.GetDbInt(dtIns.Rows[i][0]);
                            if (i == 0)
                            {
                                //插入主表
                                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@id",iId)
                                  };
                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cFullTarTable1, cColumn1, cValue1, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, param, CommandType.Text, tran);
                            }
                            //插入子表
                            SqlParameter[] param2 = new SqlParameter[]{
                new SqlParameter("@id",iId),
                 new SqlParameter("@autoid",iChild+i)
                                  };
                            sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cFullTarTable2, cColumn2, cValue2, cFrom, cfilter, cPKS, iAutoid);
                            DbHelper2.ExecuteSqlWithTrans(sql, param2, CommandType.Text, tran);

                        }
                        DbHelper2.CommitTransAndCloseConnection(tran);
                    }
                    catch (Exception ex)
                    {
                        DbHelper2.RollbackAndCloseConnection(tran);
                        //WriteLog wr = new WriteLog();
                        //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                        cError = ex.Message;
                    }
                }
                if (cError != "")
                {
                    idout = 0;
                    ccode2 = "";
                    return "error:" + cError;
                }
                idout = iId;
                ccode2 = "";
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region  中间表插入目标单据
        /// <summary>
        /// /// <summary>
        /// 中间表，生成目标单据
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string ZJBToVoucher(string cLx, int iSourceID, string cconstrB, out int idout, out string ccode2)
        {
            try
            {
                //1 是主表  2 是子表
                string cError = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句


                    //2、 insert栏目，批量插入中间表，逐条更新目标库
                    //获取id 


                    //循环+事务.  要插入的表格，依次插入到中间表   子表autoid循环
                    int iAutoid = 0;
                    string cfilter = string.Format(" where a.id='{0}'", iSourceID);
                    sql = string.Format("select b.{0} {1} {2}", cPKS, cFrom, cfilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cconstrB).Tables[0];
                    //获得目标库的account ，账套号
                    sql = " SELECT  dbo.fn_fd_getAccId()";
                    string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cconstrB));
                    //取U8的id,如果是max的取法不一样
                    if (cIdentity != "max")
                    {
                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cconstrB);
                        iId = iU8ID[0];
                        iChild = iU8ID[1];
                        iChild = iChild - dtIns.Rows.Count + 1;
                    }
                    else
                    {
                        sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPK, cTarTable1);
                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                        iId = iId + 1;
                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                        {
                            sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPKS, cTarTable2);
                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                            iChild = iId + 1;
                        }
                    }

                    //取参数,单据号、行条码、单据条码字段名称.cCodeName2 表体的单据号
                    string cIDname = "", cCodeName = "", cSysCodeName = "", cbSysCodeName = "", cCodeName2 = "";
                    string cAutoIDname = "", cRowNoName = "";
                    //第一行的id= iChild+iRowno-1；
                    int iRowno = 1;
                    List<SqlParameter> listParas1 = new List<SqlParameter>();
                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable1);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cIDname))
                    {
                        listParas1.Add(new SqlParameter("@id", iId));
                    }
                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable1);
                    cCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cCodeName))
                    {
                        listParas1.Add(new SqlParameter("@ccode", iId));
                    }
                    //cSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@csysbarcode'", cLx, cTarTable1);
                    cSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cSysCodeName))
                    {
                        listParas1.Add(new SqlParameter("@csysbarcode", iId));
                    }
                    SqlParameter[] paras1 = listParas1.ToArray();


                    //listParas2,  放到循环里去天津

                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable2);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable2);
                    cCodeName2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //rowno
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@irowno'", cLx, cTarTable2);
                    cRowNoName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cbSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@cbsysbarcode'", cLx, cTarTable2);
                    cbSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //目标表的连接事务
                    SqlTransaction tran = DbHelper2.BeginTrans(cconstrB);
                    try
                    {
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            iRowno = i + 1;
                            iAutoid = DbHelper.GetDbInt(dtIns.Rows[i][0]);
                            if (i == 0)
                            {
                                //插入主表
                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable1, cColumn1, cValue1, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, paras1, CommandType.Text, tran);
                            }
                            //构造子表的参数
                            List<SqlParameter> listParas2 = new List<SqlParameter>();
                            if (!string.IsNullOrEmpty(cIDname))
                            {
                                listParas2.Add(new SqlParameter("@id", iId));
                            }
                            if (!string.IsNullOrEmpty(cCodeName2))
                            {
                                listParas2.Add(new SqlParameter("@ccode", iId));
                            }
                            if (!string.IsNullOrEmpty(cAutoIDname))
                            {
                                listParas2.Add(new SqlParameter("@autoid", iChild + iRowno - 1));
                            }
                            if (!string.IsNullOrEmpty(cRowNoName))
                            {
                                listParas2.Add(new SqlParameter("@irowno", iRowno));
                            }
                            if (!string.IsNullOrEmpty(cbSysCodeName))
                            {
                                listParas2.Add(new SqlParameter("@cbsysbarcode", iId));
                            }
                            SqlParameter[] paras2 = listParas2.ToArray();

                            sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable2, cColumn2, cValue2, cFrom, cfilter, cPKS, iAutoid);
                            DbHelper2.ExecuteSqlWithTrans(sql, paras2, CommandType.Text, tran);

                        }
                        string cRe = GuanLian(cLx, iId, iSourceID, tran);
                        if (cRe != "ok")
                        {
                            throw new Exception("关联出错:" + cRe);
                        }

                        GenVoucherNum genVoucherNum = new GenVoucherNum(cCardnumber, iId, cconstrB);
                        ccode2 = genVoucherNum.GetNum(tran);

                        int iChongFu = 10;
                        sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                        int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        while (iCnt > 0 && iChongFu > 0)
                        {
                            //更新单据号码  ,10次还是取不到，报错                        
                            ccode2 = genVoucherNum.GetNum(tran);
                            iChongFu--;
                            sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                            iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        }
                        if (iChongFu == 0)
                        {
                            throw new Exception("获取单据编码错误！");
                        }

                        //更新单据号
                        sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cCodeName, ccode2, cIDname, iId);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);

                        //更新单据号,表体
                        if (!string.IsNullOrEmpty(cCodeName2))
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable2, cCodeName, ccode2, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        if (!string.IsNullOrEmpty(cSysCodeName) && cSysCodeName != "")
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }
                        if (!string.IsNullOrEmpty(cbSysCodeName) && cbSysCodeName != "")
                        {
                            if (!string.IsNullOrEmpty(cRowNoName) && cRowNoName != "")
                                sql = string.Format(" update {0} set {1}= '{2}'+convert(varchar(10),{3}) where  {4}='{5}'", cTarTable2, cbSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cRowNoName, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        DbHelper2.CommitTransAndCloseConnection(tran);


                    }
                    catch (Exception ex)
                    {
                        DbHelper2.RollbackAndCloseConnection(tran);
                        //WriteLog wr = new WriteLog();
                        //wr.WriteLogs(cLx + "单据生成：" + ex.Message);
                        idout = 0;
                        ccode2 = "";
                        return "error:" + ex.Message;
                    }

                }

                idout = iId;
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region  中间表更新到目标单据
        /// <summary>
        /// /// <summary>
        /// 单据更新中间表，表头更新，表体有 增加、删除、更新..暂时放弃
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string ZJBUPDATEVoucher(string cLx, int iID, int iSoucceID, out int idout, out string ccode2)
        {
            try
            {
                //iID 是主表id 
                string cError = "";
                int iChild = 0;   //插入表的子表id

                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);

                    //构造更新sql语句
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>''  and bupdate=1 and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cUpdate1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'='+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and bupdate=1 and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cUpdate2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));



                }
                idout = 0;
                ccode2 = "";
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region  单据插入目标单据
        /// <summary>
        /// /// <summary>
        /// U8单据，生成目标单据
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string VoucherToVoucher(string cLx, int iSouceId, string cfilter, string cconstrB, out int idout, out string ccode2)
        {
            try
            {
                //1 是主表  2 是子表
                string cError = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id
                ccode2 = "0";
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string ctimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    sql = string.Format(@"  SELECT STUFF((select ','+cdefaultvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cdefaultvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //构造新增等sql语句

                    //2、 insert栏目，批量插入中间表，逐条更新目标库
                    //获取id 

                    //循环+事务.  要插入的表格，依次插入到中间表   子表autoid循环
                    int iAutoid = 0;
                    //string cfilter = string.Format(" where a.id='{0}'", iSoucceID);
                    sql = string.Format("select b.{0} {1} {2}", cPKS, cFrom, cfilter);
                    DataTable dtIns = DbHelper2.Execute(sql, cconstrB).Tables[0];
                    //获得目标库的account ，账套号
                    sql = " SELECT  dbo.fn_fd_getAccId()";
                    string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cconstrB));
                    //取U8的id,如果是max的取法不一样
                    if (cIdentity != "max")
                    {
                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cconstrB);
                        iId = iU8ID[0];
                        iChild = iU8ID[1];
                        iChild = iChild - dtIns.Rows.Count + 1;
                    }
                    else
                    {
                        sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPK, cTarTable1);
                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                        iId = iId + 1;
                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                        {
                            sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPKS, cTarTable2);
                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cconstrB));
                            iChild = iId + 1;
                        }
                    }

                    //取参数,单据号、行条码、单据条码字段名称.cCodeName2 表体的单据号
                    string cIDname = "", cCodeName = "", cSysCodeName = "", cbSysCodeName = "", cCodeName2 = "";
                    string cAutoIDname = "", cRowNoName = "";
                    //第一行的id= iChild+iRowno-1；
                    int iRowno = 1;
                    List<SqlParameter> listParas1 = new List<SqlParameter>();
                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable1);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cIDname))
                    {
                        listParas1.Add(new SqlParameter("@id", iId));
                    }
                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable1);
                    cCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cCodeName))
                    {
                        listParas1.Add(new SqlParameter("@ccode", iId));
                    }
                    //cSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@csysbarcode'", cLx, cTarTable1);
                    cSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cSysCodeName))
                    {
                        listParas1.Add(new SqlParameter("@csysbarcode", iId));
                    }
                    SqlParameter[] paras1 = listParas1.ToArray();


                    //listParas2,  放到循环里去天津

                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@id'", cLx, cTarTable2);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@ccode'", cLx, cTarTable2);
                    cCodeName2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //rowno
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@irowno'", cLx, cTarTable2);
                    cRowNoName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cbSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cdefaultvalue = '@cbsysbarcode'", cLx, cTarTable2);
                    cbSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //目标表的连接事务
                    SqlTransaction tran = DbHelper2.BeginTrans(cconstrB);
                    try
                    {
                        for (int i = 0; i < dtIns.Rows.Count; i++)
                        {
                            iRowno = i + 1;
                            iAutoid = DbHelper.GetDbInt(dtIns.Rows[i][0]);
                            if (i == 0)
                            {
                                //插入主表
                                sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable1, cColumn1, cValue1, cFrom, cfilter, cPKS, iAutoid);
                                DbHelper2.ExecuteSqlWithTrans(sql, paras1, CommandType.Text, tran);
                            }
                            //构造子表的参数
                            List<SqlParameter> listParas2 = new List<SqlParameter>();
                            if (!string.IsNullOrEmpty(cIDname))
                            {
                                listParas2.Add(new SqlParameter("@id", iId));
                            }
                            if (!string.IsNullOrEmpty(cCodeName2))
                            {
                                listParas2.Add(new SqlParameter("@ccode", iId));
                            }
                            if (!string.IsNullOrEmpty(cAutoIDname))
                            {
                                listParas2.Add(new SqlParameter("@autoid", iChild + iRowno - 1));
                            }
                            if (!string.IsNullOrEmpty(cRowNoName))
                            {
                                listParas2.Add(new SqlParameter("@irowno", iRowno));
                            }
                            if (!string.IsNullOrEmpty(cbSysCodeName))
                            {
                                listParas2.Add(new SqlParameter("@cbsysbarcode", iId));
                            }
                            SqlParameter[] paras2 = listParas2.ToArray();

                            sql = string.Format(" insert into {0}({1}) select {2}  {3} {4} and b.{5}={6}", cTarTable2, cColumn2, cValue2, cFrom, cfilter, cPKS, iAutoid);
                            DbHelper2.ExecuteSqlWithTrans(sql, paras2, CommandType.Text, tran);

                        }
                        string cRe = GuanLian(cLx, iId, iSouceId, tran);
                        if (cRe != "ok")
                        {
                            throw new Exception("关联出错:" + cRe);
                        }

                        GenVoucherNum genVoucherNum = new GenVoucherNum(cCardnumber, iId, cconstrB);
                        ccode2 = genVoucherNum.GetNum(tran);

                        int iChongFu = 10;
                        sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                        int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        while (iCnt > 0 && iChongFu > 0)
                        {
                            //更新单据号码  ,10次还是取不到，报错                        
                            ccode2 = genVoucherNum.GetNum(tran);
                            iChongFu--;
                            sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                            iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                        }
                        if (iChongFu == 0)
                        {
                            throw new Exception("获取单据编码错误！");
                        }

                        //更新单据号
                        sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cCodeName, ccode2, cIDname, iId);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);

                        //更新单据号,表体
                        if (!string.IsNullOrEmpty(cCodeName2))
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable2, cCodeName, ccode2, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        if (!string.IsNullOrEmpty(cSysCodeName) && cSysCodeName != "")
                        {
                            sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }
                        if (!string.IsNullOrEmpty(cbSysCodeName) && cbSysCodeName != "")
                        {
                            if (!string.IsNullOrEmpty(cRowNoName) && cRowNoName != "")
                                sql = string.Format(" update {0} set {1}= '{2}'+convert(varchar(10),{3}) where  {4}='{5}'", cTarTable2, cbSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cRowNoName, cIDname, iId);
                            DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        }

                        DbHelper2.CommitTransAndCloseConnection(tran);


                    }
                    catch (Exception ex)
                    {
                        DbHelper2.RollbackAndCloseConnection(tran);
                        //WriteLog wr = new WriteLog();
                        //wr.WriteLogs(cLx + "单据生成：" + ex.Message);
                        idout = 0;
                        ccode2 = "";
                        return "error:" + ex.Message;
                    }

                }

                idout = iId;
                return "ok";
            }
            catch (Exception ex)
            {
                //WriteLog wr = new WriteLog();
                //wr.WriteLogs(cLx + "档案同步：" + ex.Message);
                idout = 0;
                ccode2 = "";
                return "error:" + ex.Message;

                //WriteLog(cLx+"档案同步："+ex.Message);

            }
        }
        #endregion

        #region  生单之后的处理动作，回写关联表信息
        /// <summary>
        /// 生单之后的处理动作，回写关联表信息
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <param name="iID">生成单据主键</param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public string GuanLian(string cLx, int iID, int iSoucceID, SqlTransaction tran)
        {
            string sql = "";
            string cSourceCode = "";
            switch (cLx)
            {
                case "绿驹-其他出库":
                    //update rdrecord09 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
                    //EXEC ST_VerForStock N'09',{0},0,0,1
                    sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'09',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
 update rdrecord09 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
exec IA_SP_WriteUnAccountVouchForST  {0},N'09'", iID);

                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;
                case "绿驹-其他入库":
                    //update rdrecord09 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
                    //EXEC ST_VerForStock N'09',{0},0,0,1
                    sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'08',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
 update rdrecord08 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
exec IA_SP_WriteUnAccountVouchForST  {0},N'08'", iID);

                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;
                case "绿驹-销售出库单":
                    //update rdrecord09 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
                    //EXEC ST_VerForStock N'09',{0},0,0,1
                    //更新发货单累计出库数量
                    sql = string.Format(@" 
update so_sodetails set foutquantity =  isnull(a.fOutQuantity,0)+b.iqty from so_sodetails a,  dbo.DispatchLists c,(select  b.iDLsID,SUM(isnull(b.iQuantity,0)) iqty from   dbo.rdrecord32 a,rdrecords32 b WHERE a.id  =b.id AND a.id ={0} group by iDLsID) b where c.iDLsID = b.idlsid AND a.iSOsID = c.iSOsID

update DispatchLists set fOutQuantity = ISNULL(fOutQuantity,0)+b.iqty 
FROM DispatchLists a,(select  b.iDLsID,SUM(isnull(b.iQuantity,0)) iqty from   dbo.rdrecord32 a,rdrecords32 b WHERE a.id  =b.id AND a.id ={0} group by iDLsID) b where a.iDLsID = b.idlsid

UPDATE DispatchList SET cSaleOut ='ST' FROM DispatchList A,rdrecord32 B WHERE A.dlid = B.cdlcode
AND b.id ={0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);


                    sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'32',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
 update rdrecord32 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
exec IA_SP_WriteUnAccountVouchForST  {0},N'32'", iID);

                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;
                case "绿驹-发货单":

                   
                    //更新销售订单的发货数量，发货金额
                    sql = string.Format(@" UPDATE a SET iFHQuantity=isnull(iFHQuantity,0)+b.sl,a.iFHMoney=isnull(a.iFHMoney,0)+b.je FROM dbo.SO_SODetails a
INNER JOIN (SELECT iSOsID,SUM(iQuantity) sl,SUM(isum) je FROM dbo.DispatchLists WHERE dlid ={0} GROUP BY iSOsID) b
ON a.isosid = b.iSOsID", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

//                    sql = string.Format(@"insert into SCM_entryledgerbuffer    
// (Subject,iNum,iQuantity,cWhCode,cInvCode,ItemId,    
//  cVMIVenCode,cBatch,    
//  DocumentType,DocumentId,DocumentDId,DemandPlanType,    
//  DemandPlanDId,cFree1,cFree2,cFree3,cFree4,cFree5,    
//  cFree6,cFree7,cFree8,cFree9,cFree10,dMDate,dVDate,    
//  imassunit,imassday,cBusType,cSource,ddate,iBillNum,iBillQuantity,cExpirationdate,dExpirationdate,iExpiratDateCalcu)    
// select   '0301' 
//  as Subject,      
//b.inum*(-1) as inum,    
// b.iquantity*(-1)  as iQuantity, 
//  b.cWhCode,b.cInvCode,0 as ItemId,    
//  isnull(b.cVMIVenCode,'') as cVMIVenCode ,isnull(b.cBatch,'') as cBatch,    
//  a.cvouchtype as DocumentType,a.dlid as DocumentId,b.autoid as DocumentDId,    
//0 as DemandPlanType,    
//  0 as DemandPlanDId,  
//  isnull(b.cFree1,'') as cfree1,    
//  isnull(b.cFree2,'') as cfree2,isnull(b.cFree3,'') as cfree3,isnull(b.cFree4,'') as cfree4,    
//  isnull(b.cFree5,'') as cfree5,isnull(b.cFree6,'') as cfree6,isnull(b.cFree7,'') as cfree7,    
//  isnull(b.cFree8,'') as cfree8,isnull(b.cFree9,'') as cfree9,isnull(b.cFree10,'') as cfree10,    
//  b.dMDate as dMDate,b.dVDate,    
//  b.cmassunit as imassunit,b.imassdate as imassday,a.cBusType,'销售订单',a.ddate,    
// 0 AS iBillNum,    
//  0 as iBillQuantity ,  
//  b.cExpirationdate,b.dExpirationdate ,b.iExpiratDateCalcu  
//  from dbo.DispatchList a with (nolock) inner join dbo.DispatchLists b with (nolock) on a.dlid=b.dlid     
//  left join inventory i on b.cinvcode=i.cinvcode     
// where a.dlid ={0}   ", iID);
//                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

//                    sql = string.Format(@"
//DECLARE @transactionid VARCHAR(20)      
//select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)   
//insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
// select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
// where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where Item.cInvCode=a.cInvCode and Item.cfree1=a.cfree1 
// and Item.cfree2=a.cfree2 and Item.cfree3=a.cfree3 and Item.cfree4=a.cfree4 and Item.cfree5=a.cfree5 
// and Item.cfree6=a.cfree6 and Item.cfree7=a.cfree7 and Item.cfree8=a.cfree8 and Item.cfree9=a.cfree9 and Item.cfree10=a.cfree10  )  
//                    exec dbo.Usp_SCM_CommitEntryLedger @transactionid  
//                    exec IA_SP_WriteUnAccountVouchForSA  {0},N'05'", iID);
//                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;

            
                default:
                    break;


            }
            return "ok";
        }

        #endregion
    }
}
